/*
 * AbstractCoreDB.java
 * 
 * Created by demory on Sep 19, 2009, 10:29:18 PM
 * 
 * Copyright 2008 David D. Emory
 * 
 * This file is part of Five Points. See <http://www.fpdev.org> for
 * additional information regarding the project.
 * 
 * Five Points is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * Five Points is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with Five Points.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.fpdev.core.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Date;
import org.fpdev.core.DataPackage;
import org.fpdev.core.FPEngine;
import org.fpdev.core.SysProps;

/**
 *
 * @author demory
 */
public abstract class AbstractCoreDB implements CoreDB {

  protected FPEngine engine_;
  protected DataPackage dataPkg_;

  protected boolean initialized_ = false;
  protected String user_,  pw_;
  protected Connection conn_;

  private PreparedStatement queryLastID_;

  private PreparedStatement insertLocationLogPS_;
  private PreparedStatement insertTripLogPS_;

  private PreparedStatement queryTripLinksPS_, insertTripLinksPS_, cleanupTripLinksPS_;

  public AbstractCoreDB(FPEngine engine, DataPackage dataPkg,  String user, String pw) {
    engine_ = engine;
    dataPkg_ = dataPkg;

    user_ = user;
    pw_ = pw;

    connect();
  }

  public boolean initialized() { return initialized_; }


  // METHODS LEFT TO RDBMS-SPECIFIC IMPLEMENTATIONS
  public abstract void init() throws SQLException;

  public abstract String jdbcClassname();

  public abstract void close();



  public void connect() {
    try {

      Class.forName(jdbcClassname()).newInstance();
      System.out.println("Loaded the appropriate driver.");

      init(); // initialize the connection
      if(!initialized()) {
        System.out.println("Database initialization failed");
        return;
      }

      // temp: count links as test:
      //System.out.println("Link count: " + this.countRows("links"));

      // initialize the prepared statements for later use:
      prepareStatements();

    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void prepareStatements() {
    // prepare statements
    try {

      queryLastID_ = conn_.prepareStatement("SELECT LAST_INSERT_ID()");

      if(dataPkg_.getProperty("logQueries").equals("true")) {
        insertTripLogPS_ = conn_.prepareStatement("INSERT INTO tripqueries VALUES (NULL,?,?,now(),?,?,?,?,?,?,?,?,?,?,?,?)");
        insertLocationLogPS_ = conn_.prepareStatement("INSERT INTO locqueries VALUES (NULL,?,?,now(),?,?,?)");
      }

      // server only statements:
      if(engine_.getProperty(SysProps.APP_MODE).equals("server")) {
        queryTripLinksPS_ = conn_.prepareStatement("SELECT * FROM triplinks WHERE id = ?");
        insertTripLinksPS_ = conn_.prepareStatement("INSERT INTO triplinks VALUES (NULL,now(),?)");
        cleanupTripLinksPS_ = conn_.prepareStatement("DELETE FROM triplinks WHERE servertime < ?");
      }

    } catch(Exception ex) {
      ex.printStackTrace();
    }

  }

  // TABLE CREATION/DELETION

  public void createQueryLogTables() {
    try {
      Statement s = conn_.createStatement();
      s.execute("create table tripqueries(id int primary key auto_increment, refid int, sessid int, servertime datetime, scenario int, triptype smallint, startloc varchar(128), endloc varchar(128), triptime int, tripday smallint, timemode smallint, nmspeed float, nmradius float, typefact float, topfact float, result smallint)");
      s.execute("create table locqueries(id int primary key auto_increment, refid int, sessid int, servertime datetime, scenario int, queryloc varchar(128), result smallint)");
      s.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void createTripLinksTable() {
    try {
      Statement s = conn_.createStatement();
      s.execute("create table triplinks(id int primary key auto_increment, servertime datetime, list text)");
      s.close();
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  public void dropTable(String table) {
    try {
      Statement s = conn_.createStatement();
      s.execute("drop table " + table);
    } catch (Throwable e) {
      System.out.println("exception thrown:");
      e.printStackTrace();
    }
  }

  // QUERY/UPDATE METHODS

  public void logTripQuery(int refid, int sessid, int scenid, int tripType, String start, String end, int time, String serviceID, int timeMode, double nmSpeed, double nmRadius, double bikeTypeFactor, double bikeTopFactor, int result) {
    if (!engine_.getDataPackage().getProperty("logQueries").equals("true")) {
      return;
    }

    try {
      insertTripLogPS_.setInt(1, refid);
      insertTripLogPS_.setInt(2, sessid);
      insertTripLogPS_.setInt(3, scenid);
      insertTripLogPS_.setShort(4, (short) tripType);
      insertTripLogPS_.setString(5, start);
      insertTripLogPS_.setString(6, end);
      insertTripLogPS_.setInt(7, time);
      insertTripLogPS_.setString(8, serviceID);
      insertTripLogPS_.setShort(9, (short) timeMode);
      insertTripLogPS_.setFloat(10, (float) nmSpeed);
      insertTripLogPS_.setFloat(11, (float) nmRadius);
      insertTripLogPS_.setFloat(12, (float) bikeTypeFactor);
      insertTripLogPS_.setFloat(13, (float) bikeTopFactor);
      insertTripLogPS_.setShort(14, (short) result);
      insertTripLogPS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown in CoreDB::logTripQuery():");
      e.printStackTrace();
    }
  }

  public void logLocationQuery(int refid, int sessid, int scenid, String loc, int result) {
    if (!engine_.getDataPackage().getProperty("logQueries").equals("true")) {
      return;
    }
    try {
      insertLocationLogPS_.setInt(1, refid);
      insertLocationLogPS_.setInt(2, sessid);
      insertLocationLogPS_.setInt(3, scenid);
      insertLocationLogPS_.setString(4, loc);
      insertLocationLogPS_.setShort(5, (short) result);
      insertLocationLogPS_.executeUpdate();
    } catch (Throwable e) {
      System.out.println("exception thrown in CoreDB::logLocationQuery():");
      e.printStackTrace();
    }
  }

  public int initTripLinks(String links) {
    try {
      insertTripLinksPS_.setString(1, links);
      insertTripLinksPS_.executeUpdate();
      ResultSet rs = queryLastID_.executeQuery(); //s_.executeQuery("SELECT LAST_INSERT_ID()");
      if (rs.next()) return rs.getInt(1);
    } catch (Throwable e) {
      System.out.println("exception thrown in CoreDB::initTripLinks():");
      e.printStackTrace();
    }
    return 0;
  }

  public String getLinkList(int id) {
    String result = null;
    try {
      queryTripLinksPS_.setInt(1, id);
      ResultSet rs = queryTripLinksPS_.executeQuery();
      rs.next();
      result = rs.getString(3);
      rs.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    return result;
  }

  public void cleanup() {
    try {
      Calendar cal = Calendar.getInstance();
      cal.add(Calendar.HOUR, -24);
      Date date = cal.getTime();
      cleanupTripLinksPS_.setDate(1, new java.sql.Date(date.getTime()));
      cleanupTripLinksPS_.executeUpdate();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public void ping() {
    try {
      Statement s = conn_.createStatement();
      String query = "INSERT INTO ping VALUES (1, 'foo')";
      s.executeUpdate(query);

      query = "DELETE FROM ping WHERE id = 1";
      s.executeUpdate(query);

      s.close();

    } catch (Throwable e) {
      System.out.println("exception thrown in CoreDB::ping()");
      e.printStackTrace();
    }
  }


}
